home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Freelog Special Freeware 31
/
FreelogHS31.iso
/
ArgentCompta
/
FASTCAISSE
/
Setup FASTCAISSE.exe
/
{app}
/
A Faire42.SQL
< prev
next >
Wrap
Text File
|
2003-11-03
|
2KB
|
70 lines
SET GENERATOR VERSION_BASE TO 42;
ALTER PROCEDURE "MAJSTOCK"
(
"INDEXP" INTEGER,
"DELTA" SMALLINT
)
AS
DECLARE VARIABLE V1 SMALLINT;
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE Q DECIMAL(11,2);
BEGIN
SELECT TYPESTOCK FROM PIECES WHERE INDX=:INDEXP AND TYPESTOCK<>1 AND STATUT IN (0,3) INTO V1;
IF (V1 IS NOT NULL) THEN
FOR SELECT INDEXARTICLE,SUM(QUANTITE) FROM LIGNES
WHERE INDEXPIECE=:INDEXP
AND INDEXARTICLE IS NOT NULL AND INDEXARTICLE>0
AND QUANTITE IS NOT NULL AND QUANTITE<>0
GROUP BY INDEXARTICLE
INTO :I,:Q
DO
BEGIN
Q=Q*:DELTA;
IF (V1=4) THEN V1=0;
IF (V1=0) THEN /* TICKET, FACTURE */
UPDATE ARTICLES SET DISPO=DISPO-:Q WHERE INDX=:I;
ELSE IF (V1=2) THEN /* RESERVATION */
UPDATE ARTICLES SET DISPO=DISPO-:Q,RESERVE=RESERVE+:Q WHERE INDX=:I;
ELSE IF (V1=3) THEN /* BL */
UPDATE ARTICLES SET DISPO=DISPO-:Q,BL_CLIENT=BL_CLIENT+:Q WHERE INDX=:I;
ELSE IF (V1=5) THEN /* PRET */
UPDATE ARTICLES SET DISPO=DISPO-:Q,EN_PRET=EN_PRET+:Q WHERE INDX=:I;
ELSE IF (V1=6) THEN /* SAV */
UPDATE ARTICLES SET EN_SAV=EN_SAV+:Q WHERE INDX=:I;
ELSE IF (V1=7) THEN /* RETOUR SAV */
UPDATE ARTICLES SET EN_SAV=EN_SAV-:Q WHERE INDX=:I;
ELSE IF (V1=8) THEN /* AVOIR */
UPDATE ARTICLES SET DISPO=DISPO+:Q WHERE INDX=:I;
ELSE IF (V1=13) THEN /* COMMANDE */
UPDATE ARTICLES SET EN_COMMANDE=EN_COMMANDE+:Q WHERE INDX=:I;
ELSE IF (V1=14) THEN /* LIVRAISON */
UPDATE ARTICLES SET DISPO=DISPO+:Q WHERE INDX=:I;
END /* DO */
END
;
CREATE VIEW "QTE_ENCOMMANDE" (
"INDEXARTICLE",
"QTE"
) AS
SELECT INDEXARTICLE,SUM(QUANTITE) FROM PIECES
LEFT JOIN LIGNES L ON L.INDEXPIECE=INDX
WHERE TYPEPIECE=13 AND STATUT=0 AND INDEXARTICLE<>0 AND QUANTITE<>0
GROUP BY INDEXARTICLE
;
CREATE VIEW "QTE_ENPRECOMMANDE" (
"INDEXARTICLE",
"QTE"
) AS
SELECT INDEXARTICLE,SUM(QUANTITE) FROM PIECES
LEFT JOIN LIGNES L ON L.INDEXPIECE=INDX
WHERE TYPEPIECE=13 AND STATUT=2 AND INDEXARTICLE<>0 AND QUANTITE<>0
GROUP BY INDEXARTICLE
;